set more off
set scheme dklean
tempfile buffer

*===============================================================================
* Combined do file for figures
*===============================================================================

cd "./"

*=====
*Define functions

*Count number of insurers
capt prog drop count_instances
program count_instances
	version 14.0
	syntax varname [if], sname(string)
	
	marksample touse
	egen NO = count(`varlist') if `touse', by(`varlist')
	replace NO = 0 if !`touse'
	replace NO = 1/NO
	egen tot = total(NO)
	estadd scalar `sname' = tot[_N]
	drop NO tot
	
end

*Double cluster SEs with areg
capt prog drop thompson_areg
program thompson_areg, eclass
	version 13.1
    syntax anything [if], clusd1(varname) clusd2(varname) avar(varname)
	 
	tempname varro vard1 vard2 doubleclus
	
	marksample touse
	areg `anything' if `touse', absorb(`avar') vce(robust)
	mat `varro' = e(V)
	
	qui areg `anything' if `touse', absorb(`avar') vce(cluster `clusd1')
	mat `vard1' = e(V)
	
	qui areg `anything' if `touse', absorb(`avar') vce(cluster `clusd2')
	mat `vard2' = e(V)
	
	mat `doubleclus' = `vard1' + `vard2' - `varro'
	
	ereturn repost V = `doubleclus'

end

*==========================
*==========================
* For Figure 1: Private equity activity in insurace
*=====
tempfile exitdata

import delimited using "Data/PE_ownership_data.csv", clear

keep naic peend

replace peend = "" if peend=="12/1/2016"

gen see_exit = 0
replace see_exit = 1 if peend!=""

save `exitdata'

*=====

*From PE Share Prep.do
use Data/Asset_Mix_Prep.dta, clear

keep if quarter(dofq(Quarter))==4
gen year = yofd(dofq(Quarter))

ren NAIC_Code naic
xtset naic year

merge 1:1 naic year using Data/Pe_status.dta, nogen keep(match)

keep year naic assets pe_status

*Convert assets to $BN
replace assets = assets/1E6
format assets %9.1fc

egen maxpe = max(pe_status), by(naic)
keep if maxpe==1

gen entry = pe_status==1 & L.pe_status==0

gen exit = pe_status==0 & L.pe_status==1
egen maxexit = max(exit), by(naic)

merge m:1 naic using `exitdata', nogen keep(master match)

replace maxexit = 1 if maxexit==0 & see_exit==1

save `buffer'

collapse (sum) entryassets = assets if entry==1, by(year)
tempfile step
save `step'

use `buffer', clear

collapse (sum) exitassets = assets if entry==1 & maxexit==1, by(year)
merge 1:1 year using `step', nogen

replace exitassets = 0 if mi(exitassets)
replace exitassets = -exitassets

gen flat = 0

tsset year

*Construct Figure 1: Private equity in insurance

graph bar entryassets exitassets, stack over(year)  ///
ytitle("PE activity in life insurance ({c $|}BN)") ysize(2.5) ylab(-25(25)100, nogrid) /// 
legend(row(1) order(1 "Entry" 2 "Subsequent exit")) yline(0,lstyle(grid))

twoway (rbar flat entryassets year, barwidth(0.6)) (rbar exitassets flat year, barwidth(0.6)), ysize(2.5) ylab(-25(25)100, nogrid) legend(row(1) order(1 "Entry" 2 "Subsequent exit")) xtitle("") ytitle("PE activity in life insurance ({c $|}BN)") xline(2009.5, lcolor(gs8) lpattern(dash)) xlabel(2006(1)2014)

graph export Output/PE_entry_exit_v2.png, replace


*==========================
*==========================
* For Figure 2: Quarterly portfolio composition in event time

cls

use Data/Asset_Mix_Prep.dta, clear

xtset NAIC_Code Quarter

*Define PE groups, data has been anonymized 
label define PE_ID 1 "PE Group A" 2 "PE Group B" 3 "PE Group C" 4 "Other PE" 5 "Non PE"
label values PE_ID PE_ID

label var pe_status "PE $\times$ After"

*Drop if always PE
egen cQ = count(Quarter), by(NAIC_Code)
egen TPE = total(pe_status), by(NAIC_Code)
drop if (cQ==TPE & pe_status==1)
drop cQ TPE

gen lntotFV = ln(TotalFV)
gen BondGrowth = 100*(lntotFV-L.lntotFV)

label var BondGrowth "$\Delta \ln$ Bonds"

xtset NAIC_Code Quarter
gen pe_q = pe_status==1 & L.pe_status==0
gen pe_qm1 = F.pe_q
gen pe_qm2 = F2.pe_q
gen pe_qm3 = F3.pe_q
gen pe_qm4 = F4.pe_q
gen pe_qp1 = L.pe_q
gen pe_qp2 = L2.pe_q
gen pe_qp3 = L3.pe_q
gen pe_qp4 = L4.pe_q
gen pe_qlater = pe_status==1 & pe_q==0 & pe_qp1==0 & pe_qp2==0 & pe_qp3==0 & pe_qp4==0

label var pe_qm4 "-4"
label var pe_qm3 "-3"
label var pe_qm2 "-2"
label var pe_qm1 "-1"
label var pe_q "0"
label var pe_qp1 "1"
label var pe_qp2 "2"
label var pe_qp3 "3"
label var pe_qp4 "4"
label var pe_qlater ">=5"

foreach v of varlist pe_q* {
	replace `v' = 0 if mi(`v')
}

*Winsorize full shares
winsor2 SAClass* SNCat* YTMAgg YTMAClass* DiscountAClass*, replace cuts (5 95) by(pe_status)
winsor2 BondGrowth, replace cuts (5 95) by(pe_status)

gen Dum10_14 = inrange(Quarter,tq(2010q1),tq(2014q4))
label var Dum10_14 "2010-2014"

*==============================
cls

keep if !mi(TotalPLABSFV) & Quarter>=tq(2007q4)

*Winsorize ABS shares
winsor2 PLABSSNCat* PLABSSANCat* PLABSDiscNCAT*, replace cuts (5 95) by(pe_status)

egen cQ = count(Quarter), by(NAIC_Code)
egen TPE = total(pe_status), by(NAIC_Code)
drop if (cQ==TPE & pe_status==1)
drop cQ TPE

*Alternate NAIC Category: pre-trends

gen PLABSSANCat1_2 = PLABSSANCat1+PLABSSANCat2
gen PLABSSANCat3_6 = PLABSSANCat3+PLABSSANCat4+PLABSSANCat5+PLABSSANCat6
gen PLABSSANCat5_6 = PLABSSANCat5+PLABSSANCat6

label var PLABSSANCat1_2 "NAIC 1-2"
label var PLABSSANCat3_6 "NAIC 3-6"
label var PLABSSANCat5_6 "NAIC 5-6"

*Construct Figure 2 Panel A: Private-label ABS/General account bonds

qui thompson_areg SAClass5 pe_qm4 pe_qm3 pe_qm2 pe_qm1 pe_q pe_qp1 pe_qp2 pe_qp3 pe_qp4 pe_qlater i.Quarter, avar(NAIC_Code) clusd1(Quarter) clusd2(NAIC_Code)

coefplot, keep(pe_q*) vertical ysize(2.5) ciopts(recast(rcap)) yline(0,lstyle(grid)) ytitle("") xtitle("Quarters since and from acquisition")
graph export Output/Pre_Trends_AClass.png, replace

*Construct Figure 2 Panel B: Alternate NAIC class

qui thompson_areg PLABSSANCat3_6 pe_qm4 pe_qm3 pe_qm2 pe_qm1 pe_q pe_qp1 pe_qp2 pe_qp3 pe_qp4 pe_qlater i.Quarter, avar(NAIC_Code) clusd1(Quarter) clusd2(NAIC_Code)

coefplot, keep(pe_q*) vertical ysize(2.5) ciopts(recast(rcap)) yline(0,lstyle(grid)) ytitle("") xtitle("Quarters since and from acquisition")
graph export Output/Pre_Trends_AltNAIC.png, replace


*==========================
*==========================
* For Figure 3: Quarterly portfolio composition in event time

cls
*=====
*Load transaction data
import delimited using "Data/PE_ownership_data.csv", clear

gen PEDate = date(pestart, "MDY")
format PEDate %td

drop if PEDate == .
keep naic PEDate
rename naic NAIC_Code

save "Data/PE_transaction_temp.dta", replace

use Data/Daily_trading_ABSType.dta, clear

*Transform date to week format
gen Week = yw(year(Date), week(Date))
gen Quarter = yq(year(Date), quarter(Date))
format Week %tw
format Quarter %tq

drop Date

*Collapse data at weekly level
collapse (sum) NetFV=NetFV (mean) Quarter, by(NAIC_Code Week ABSType)
sort NAIC_Code ABSType Week

*Reshape data from long to wide to separate different ABSTypes
reshape wide NetFV Quarter, i(NAIC_Code Week) j(ABSType)

egen Quarter = rowmean(Quarter1 Quarter2 Quarter3 Quarter4)
format Quarter %tq
drop Quarter1 Quarter2 Quarter3 Quarter4

merge n:1 NAIC_Code using "Data/PE_transaction_temp.dta", keepusing(PEDate)
drop if _merge == 2
drop _merge

*Identify trading before and after acquisition
gen PEWeek = yw(year(PEDate), week(PEDate)) 
format PEWeek %tw
gen pe_status = 0
replace pe_status = 1 if Week>=PEWeek

drop PEDate

save "Data/PE_trading_temp.dta", replace

use "Data/Asset_Mix_Prep.dta", clear

xtset NAIC_Code Quarter
gen TotalFV_lastq = L.TotalFV

save "Data/Asset_Mix_temp.dta", replace

use "Data/PE_trading_temp.dta", clear

merge n:n NAIC_Code Quarter using "Data/Asset_Mix_temp.dta", keepusing(TotalFV TotalFV_lastq)
drop if _merge!=3
drop _merge

sort NAIC_Code Week

xtset NAIC_Code Week

tsfill

replace NAIC_Code = L.NAIC_Code if NAIC_Code == .

replace pe_status = L.pe_status if pe_status == .

*Drop if always PE owned
egen cW = count(Week), by(NAIC_Code)
egen TPE = total(pe_status), by(NAIC_Code)
drop if (cW==TPE & pe_status==1)
drop cW TPE

*Generate net trading as a percentage of total bond holdings last quarter
forval i = 1/4 {
	gen Trading_Percent`i' = (NetFV`i'/TotalFV_lastq)*100
	replace Trading_Percent`i' = 0 if Trading_Percent`i' == .
	label var Trading_Percent`i' "ABS Type `i'"
}

*Combine ABS category 1 and 3
gen Trading_Percent5 = Trading_Percent1 + Trading_Percent3
label var Trading_Percent5 "ABS Type 1 and 3"
gen Trading_PercentAll = Trading_Percent1 + Trading_Percent2 + Trading_Percent3 + Trading_Percent4
label var Trading_PercentAll "All ABS Types"

gen pe_w = pe_status==1 & L.pe_status==0
gen pe_wm1 = F.pe_w
gen pe_wm2 = F2.pe_w
gen pe_wm3 = F3.pe_w
gen pe_wm4 = F4.pe_w
gen pe_wm5 = F5.pe_w
gen pe_wm6 = F6.pe_w
gen pe_wm7 = F7.pe_w
gen pe_wm8 = F8.pe_w
gen pe_wm9 = F9.pe_w
gen pe_wm10 = F10.pe_w
gen pe_wm11 = F11.pe_w
gen pe_wm12 = F12.pe_w

gen pe_wp1 = L.pe_w
gen pe_wp2 = L2.pe_w
gen pe_wp3 = L3.pe_w
gen pe_wp4 = L4.pe_w
gen pe_wp5 = L5.pe_w
gen pe_wp6 = L6.pe_w
gen pe_wp7 = L7.pe_w
gen pe_wp8 = L8.pe_w
gen pe_wp9 = L9.pe_w
gen pe_wp10 = L10.pe_w
gen pe_wp11 = L11.pe_w
gen pe_wp12 = L12.pe_w

*Label variables
forval i = 1/12 {
	label var pe_wm`i' "-`i'"
	label var pe_wp`i' "`i'"
}
label var pe_w "0"

replace pe_w = 0 if pe_w == .
forval i = 1/12 {
	replace pe_wm`i' = 0 if pe_wm`i' == .
	replace pe_wp`i' = 0 if pe_wp`i' == .
}

preserve

*=====
winsor2 Trading_Percent*, replace cuts (5 95) by(pe_status)

eststo clear

*Construct Figure 3 Panel A: ABS Type 5

qui thompson_areg Trading_Percent5 pe_wm8 pe_wm7 pe_wm6 pe_wm5 pe_wm4 pe_wm3 pe_wm2 pe_wm1 ///
			pe_w pe_wp1 pe_wp2 pe_wp3 pe_wp4 pe_wp5 pe_wp6 pe_wp7 pe_wp8 ///
			i.Week, avar(NAIC_Code) clusd1(Quarter) clusd2(NAIC_Code)

	coefplot, keep(pe_w*) vertical ysize(2.5) ciopts(recast(rcap)) yline(0,lstyle(grid)) ytitle("") xtitle("Weeks since and from acquisition: ABS Type 1 and 3")
	graph export Output/Pre_Trends_8Weeks_ABSType5.png, replace
	
*Construct Figure 3 Panel B: All ABS Types
	
qui thompson_areg Trading_PercentAll pe_wm8 pe_wm7 pe_wm6 pe_wm5 pe_wm4 pe_wm3 pe_wm2 pe_wm1 ///
			pe_w pe_wp1 pe_wp2 pe_wp3 pe_wp4 pe_wp5 pe_wp6 pe_wp7 pe_wp8 ///
			i.Week, avar(NAIC_Code) clusd1(Quarter) clusd2(NAIC_Code)

	coefplot, keep(pe_w*) vertical ysize(2.5) ciopts(recast(rcap)) yline(0,lstyle(grid)) ytitle("") xtitle("Weeks since and from acquisition: All ABS Types")
	graph export Output/Pre_Trends_8Weeks_ABSTypeAll.png, replace

*==========================
*==========================
* For Figure 4: Daily private-label ABS transcations in event time
cls
*=====
use Data/EventTime_PE_Transactions.dta, clear

keep if inrange(Date,-120,120)
order PE_NAIC Date cumABSNetPurch1 cumABSNetPurch2 cumABSNetPurch3 /// 
cumABSNetPurch4 cumNonPEABSNetPurch1 cumNonPEABSNetPurch2 cumNonPEABSNetPurch3 cumNonPEABSNetPurch4

gen cumABSNetPurchx = cumABSNetPurch1+cumABSNetPurch2+cumABSNetPurch3+cumABSNetPurch4
gen cumNonPEABSNetPurchx = cumNonPEABSNetPurch1+cumNonPEABSNetPurch2+cumNonPEABSNetPurch3+cumNonPEABSNetPurch4

winsor2 cumABSNetPurch* cumNonPEABSNetPurch*, cuts(10 90) replace

format cumABSNetPurch* cumNonPEABSNetPurch* %9.0fc

local cat = 4

*preserve

collapse (mean) cumABSNetPurch1 cumABSNetPurch2 cumABSNetPurch3 /// 
cumABSNetPurch4 cumABSNetPurchx  cumNonPEABSNetPurch1 cumNonPEABSNetPurch2 /// 
cumNonPEABSNetPurch3 cumNonPEABSNetPurch4 cumNonPEABSNetPurchx , by(Date)

foreach v of varlist cumABSNetPurch1 cumABSNetPurch2 cumABSNetPurch3 /// 
cumABSNetPurch4 cumABSNetPurchx cumNonPEABSNetPurch1 cumNonPEABSNetPurch2 /// 
cumNonPEABSNetPurch3 cumNonPEABSNetPurch4 cumNonPEABSNetPurchx  {

local start = `v'[1]
replace `v' = `v' - `start'

}

*Construct Figure 4 Panel A: PE transactions
tsline cumABSNetPurch1 cumABSNetPurch3 cumABSNetPurch2 cumABSNetPurch4, ysize(2.5) ///
tlabel(-120(60)120, format(%9.0fc)) ttitle("Days since and from acquisition") ///
ytitle("Cumulative net purchases/Assets (%)") ylabel(-1(1)4) ///
tline(0, lcolor(gs8) lpattern(dash)) lwidth(medthick medthick medthick medium) ///
legend(order(1 "NAIC 1, Alt NAIC 4-6" 2 "No Alt NAIC"  /// 
3 "Other Alt NAIC>NAIC" 4 "Other private-label ABS") col(1) ring(0) pos(11))

graph export Output/Daily_mean_PE.png, replace

*Construct Figure 4 Panel B: Non-PE transactions
tsline cumNonPEABSNetPurch1 cumNonPEABSNetPurch3 cumNonPEABSNetPurch2 cumNonPEABSNetPurch4, ysize(2.5) ///
tlabel(-120(60)120, format(%9.0fc)) ttitle("Days since and from acquisition") ///
ytitle("Cumulative net purchases/Assets (%)") ylabel(-1(1)4) ///
tline(0, lcolor(gs8) lpattern(dash)) lwidth(medthick medthick medthick medium) ///
legend(order(1 "NAIC 1, Alt NAIC 4-6" 2 "No Alt NAIC"  /// 
3 "Other Alt NAIC>NAIC" 4 "Other private-label ABS") col(1) ring(0) pos(11))

graph export Output/Daily_mean_nonPE.png, replace

*==========================
*==========================
* For Figure 5: New shadow reinsurance contracts
cls
*=====

use Data/Reinsurance_data.dta if ceded & netReserve>0, clear
capture drop _merge 
merge m:1 naic year using Data/Pe_status.dta

egen max_pe_status = max(pe_status), by(naic)
drop if max_pe_status == 2 
replace pe_status = 0 if pe_status == .

capture drop _merge

*Define PE groups, data has been anonymized 
label define PE_ID 1 "PE Group A" 2 "PE Group B" 3 "PE Group C" 4 "Other PE" 5 "Non PE"
label values PE_ID PE_ID

table year pe_status if shadow==1, c(sum netReserve_new) f(%9.1fc)
table year PE_ID if pe_status==1 & shadow==1, c(sum netReserve_new) f(%9.1fc)

label define pe_status 0 "Non-PE" 1 "PE"
label values pe_status pe_status

gen Annuity_Reins = 0
replace Annuity_Reins = 1 if regexm(reins_type,"ACO")|regexm(reins_type,"AMCO")

*Construct Figure 5 Panel A: All new shadow insurnace
gen FullShadowType = .
replace FullShadowType = 1 if Annuity_Reins==1 & genaccount==1 & shadow==1
replace FullShadowType = 2 if Annuity_Reins==1 & genaccount==0 & shadow==1
replace FullShadowType = 3 if Annuity_Reins==0 & shadow==1

label define FullShadowType 1 "Fixed annuities" 2 "Variable annuities" 3 "Life insurance"
label values FullShadowType FullShadowType

graph bar (sum) netReserve_new if !mi(FullShadowType) & first & inrange(year,2009,2014), /// 
over(FullShadowType) over(year) asyvars stack ///
ytitle("New shadow reinsurance ({c $|}BN)") ysize(2.5) ylab(,nogrid) ///
legend(row(1))

graph export Output/New_Shadow_ByType.png, replace

*Construct Figure 5 Panel B: New fixed annuity shadow reinsurance
table pe_status if !mi(FullShadowType) & first & inrange(year,2011,2014), /// 
c(sum netReserve_new) f(%9.1fc) row

graph bar (sum) netReserve_new if FullShadowType==1 & first & inrange(year,2009,2014), /// 
over(PE_ID) over(year) asyvars stack ///
ytitle("New shadow reinsurance ({c $|}BN)") ysize(2.5) ylab(,nogrid) ///
legend(row(1))

graph export Output/New_GenAnnShadow_ByPE.png, replace

*==========================
*==========================
* For Figure 6: Price of 5 year guaranteed annuities
cls
*=====

import excel using "Data/Annuity_pricing.xlsx", sheet("Sheet1") firstrow clear

gen Date = td(07apr2016)
format Date %td

gen StepRate = regexm(RS,"Step")
replace RS = "" if StepRate
destring RS, replace

drop if Withdrawn=="W"
drop Withdrawn

*Define PE groups, data has been anonymized 
gen A = regexm(Company,"Group A")|regexm(Company,"Group B")
gen B = regexm(Company,"Group C")|regexm(Company,"Group D")|regexm(Company,"Group E")
gen C = regexm(Company,"Group F")

drop if Rating!=""

label define YG 5 "5Y MYGA" 7 "7Y MYGA"
label values YG YG

gen Type = 0
replace Type = 1 if A|B|C

label define Type 0 "Other insurers" 1 "PE insurers"
label values Type Type

*CMS yields from Treasury as of 4/7/2017
local Yield5Y = 1.92
local Yield7Y = 2.20

table Type YG, row

winsor2 GYTS, by(YG) replace cuts (5 95)

sum GYTS if Type==1 & YG==5, detail
sum GYTS if Type==0 & YG==5, detail

*Construct Figure 6 Panel A
twoway (histogram GYTS if Type==1 & YG==5, start(1.25) width(0.2) frac) /// 
(histogram GYTS if Type==0 & YG==5, start(1.25) width(0.2) fcolor(none) frac), ysize(2.5) ///
xtitle("Guaranteed yield (%)") xlabel(1.25(0.5)3) ///
legend(order(2 "Other insurers" 1 "PE insurers"))

graph export Output/Annuity_Pricing_5Y_Hist.png, replace

gen PType = .
replace PType = 1 if regexm(Product,"[Hh]igh")&regexm(Product,"[Bb]and")
replace PType = 2 if regexm(Product,"[Mm]id")&regexm(Product,"[Bb]and")
replace PType = 3 if regexm(Product,"[Ll]ow")&regexm(Product,"[Bb]and")

gen MVA = regexm(Product,"MVA")

keep if inlist(PType,1,2) & MVA==1

table Type YG, row

gen Type2 = regexm(Company,"Group G")
label define Type2 0 "Other insurers" 1 "PE insurers"
label values Type2 Type2

sum GYTS if Type2==1 & YG==5, detail
sum GYTS if Type2==0 & YG==5, detail

*Construct Figure 6 Panel B
twoway (histogram GYTS if Type2==1 & YG==5, start(1.25) width(0.2) frac) /// 
(histogram GYTS if Type2==0 & YG==5, start(1.25) width(0.2) fcolor(none) frac), ysize(2.5) ///
xtitle("Guaranteed yield (%)") xlabel(1.25(0.5)3) ///
legend(order(2 "Other insurers" 1 "PE insurers"))

graph export Output/Annuity_Pricing_5Y_SubType_Hist.png, replace

*==========================
*==========================
* For Figure 7: RBC Ratio
cls
tempfile buffer
*=====

use Data/Asset_Mix_Prep.dta, clear

tempfile ABS_NAIC
preserve

keep if !mi(TotalPLABSFV) & Quarter>=tq(2007q4)
contract NAIC_Code
keep NAIC_Code
save `ABS_NAIC'
restore

*Keep 2014Q4 only
keep if Quarter==tq(2014q4)
gen Year = 2014

keep NAIC_Code Year Req_RBC RBC_Reduction pe_status
ren Req_RBC Req_R1
ren RBC_Reduction R1_Reduction
replace R1_Reduction = 0 if R1_Reduction<0

label define pe_status 0 "Non-PE" 1 "PE"
label values pe_status pe_status

save `buffer'

import excel using "Data/Insurer_chars.xlsx", sheet("Sheet1") cellrange(A4) clear

keep if C=="PY(0)"

keep B F G-N
ren (B F G-N) (Year NAIC_Code Assets Liab_ex_sep Liabilities /// 
Reserves_GenAcct Reserves_Annuity Net_Income TAC ACL_RBC)

replace Year = regexr(Year,"Y","")
destring Year, replace

*Destring and convert to $BN
foreach v of varlist Assets-ACL_RBC {
		capture replace `v'="" if indexnot(`v',"0123456789.-")
	capture destring `v', replace
	replace `v' = `v'/1E6
}

drop if mi(Assets,Net_Income,TAC,ACL_RBC)

keep NAIC_Code Year Assets Net_Income TAC ACL_RBC

keep if Year==2014

format Assets-ACL_RBC %9.2fc

gen RBC_Ratio = 100*TAC/(2*ACL_RBC)

format RBC_Ratio %9.0fc

merge 1:1 NAIC_Code using `buffer', nogen keep(match)

*Lower bound capital adjustment
gen R2to5Sq = (ACL_RBC)^2 - Req_R1^2
replace R2to5Sq = 0 if R2to5Sq<0
gen RBC_Reduction_LB = sqrt((Req_R1+R1_Reduction)^2+R2to5Sq) - sqrt(Req_R1^2+R2to5Sq)

gen Hyp_ACL_RBC = ACL_RBC+RBC_Reduction_LB

gen Hyp_TAC = RBC_Ratio*(2*Hyp_ACL_RBC)/100

*RBC ratio with no change to TAC
gen Hyp_RBC_Ratio = 100*TAC/(2*Hyp_ACL_RBC)

gen ROE = Net_Income/TAC*100
gen HROE1 = Net_Income/Hyp_TAC*100

winsor2 ROE HROE1, by(pe_status) replace cuts (10 90)
**********
*Rating distributions

*Merge AMBest rating
merge 1:1 NAIC_Code using Data/Ratings.dta, nogen keep(match master)

*Do analysis within rated firms
drop if AMBest_Rating=="NR" | mi(AMBest_Rating)

gen AMBestNum = .
replace AMBestNum = 1 if AMBest_Rating=="A++"
replace AMBestNum = 2 if AMBest_Rating=="A+"
replace AMBestNum = 3 if AMBest_Rating=="A"
replace AMBestNum = 4 if AMBest_Rating=="A-"
replace AMBestNum = 5 if AMBest_Rating=="B++"
replace AMBestNum = 6 if AMBest_Rating=="B+"
replace AMBestNum = 7 if AMBest_Rating=="B"
replace AMBestNum = 8 if AMBest_Rating=="B-"
replace AMBestNum = 9 if AMBest_Rating=="C++"
replace AMBestNum = 10 if AMBest_Rating=="C+"
replace AMBestNum = 11 if AMBest_Rating=="C"
replace AMBestNum = 12 if AMBest_Rating=="C-"
replace AMBestNum = 13 if AMBest_Rating=="D"

label define AMBestNum 1 "A++" 2 "A+" 3 "A" 4 "A-" 5 "B++" 6 "B+" 7 "B" 8 "B-" ///
9 "C++" 10 "C+" 11 "C" 12 "C-" 13 "D"
label values AMBestNum AMBestNum

replace RBC_Ratio = 600 if RBC_Ratio>600
replace Hyp_RBC_Ratio = 600 if Hyp_RBC_Ratio>600

replace RBC_Ratio = 10 if RBC_Ratio<10
replace Hyp_RBC_Ratio = 10 if Hyp_RBC_Ratio<10

expand 2 if pe_status==1, gen(newobs)
replace RBC_Ratio = Hyp_RBC_Ratio if newobs==1

gen AMBestBuck = .
replace AMBestBuck = 1 if inrange(AMBestNum,4,5) & pe_status==0
replace AMBestBuck = 2 if inrange(AMBestNum,6,7) & pe_status==0
replace AMBestBuck = 3 if inrange(AMBestNum,8,13) & pe_status==0
replace AMBestBuck = 4 if inrange(AMBestNum,4,5) & pe_status==1 & newobs==0
replace AMBestBuck = 5 if inrange(AMBestNum,4,5) & pe_status==1 & newobs==1

label define AMBestBuck 1 "A- or B++" 2 "B+ or B" 3 "B- or worse" 4 "PE (actual)" 5 "PE (adjusted)"
label values AMBestBuck AMBestBuck

*Construct Figure 6: RBC ratios (2014)
graph box RBC_Ratio, over(pe_status) over(AMBestBuck) asyvars ysize(2.5) ytitle("RBC ratio (%)") ylab(,nogrid) yline(150,lpattern(shortdash)) boxgap(-100)
graph export Output/RBC_by_rating.png, replace


